Convert Start Time & End Time in multiple Rows in M Code

Hi lync0056 ,

We can meet your requirement in Power Query Editor.

1. Firstly, we have a table.

2. then we add a column to calculate the interval counts.

Duration.TotalMinutes([EndTime]-[StartTime])/15)

3. Then we add another list to contain all the time between this time, you can read this document about this function.

List.DateTimes([StartTime],[Custom]+1,#duration(0, 0, 15, 0))

(BTW, you can also change the Custom1 column to the formula Duration.TotalMinutes([EndTime]-[StartTime])/15))

4. expand the list

5. remove the column you do not want and rename the new column

the Complete M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMDQ0t9C31jAwVDKwMDIFJIzEURtkQIx+pEKzmBJLHqK0DWZ4hsXGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, StartTime = _t, EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.TotalMinutes([EndTime]-[StartTime])/15),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.DateTimes([StartTime],[Custom]+1,#duration(0, 0, 15, 0))),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"StartTime", "EndTime", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Time"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.